﻿using System;
using System.Data;
using System.Data.OleDb;
using System.Windows.Forms;

namespace ToolUtil
{
    public class OfficeTool
    {
        private static string ShowSaveFileDialog(string sct)
        {
            string localFilePath = "";
            SaveFileDialog sfd = new SaveFileDialog();
            //设置文件类型 
            sfd.Filter = "Excel文件| *.xlsx;*.xls";
            //设置默认文件类型显示顺序 
            sfd.FilterIndex = 1;
            //保存对话框是否记忆上次打开的目录 
            sfd.RestoreDirectory = true;
            sfd.FileName = sct + DateTime.Now.ToString("yyyyMMdd");
            //点了保存按钮进入 
            if (sfd.ShowDialog() == DialogResult.OK)
            {
                localFilePath = sfd.FileName.ToString(); //获得文件路径 
            }
            return localFilePath;
        }
        /// <summary>
        /// 将DataGridView导出excel
        /// </summary>
        /// <param name="dataGridBook"></param>
        public static void DgvToExcel(DataGridView dataGridBook)
        {
            dataGridBook.CommitEdit(DataGridViewDataErrorContexts.Commit);
            try
            {
                Microsoft.Office.Interop.Excel.Application application = new Microsoft.Office.Interop.Excel.Application();
                application.Application.Workbooks.Add(true);
                for (int i = 0; i < dataGridBook.Columns.Count; i++)
                {
                    application.Cells[1, i + 1] = dataGridBook.Columns[i].HeaderText;
                }
                FrmProcess process2 = new FrmProcess();
                process2.progressBar1.Maximum = dataGridBook.Rows.Count;
                FrmProcess process = process2;
                process.ShowInTaskbar = false;
                process.Show();
                for (int j = 0; j < dataGridBook.Rows.Count; j++)
                {
                    System.Windows.Forms.Application.DoEvents();
                    process.progressBar1.Value = j;
                    process.label1.Text = (j + 1).ToString();
                    for (int k = 0; k < dataGridBook.Columns.Count; k++)
                    {
                        application.Cells[j + 2, k + 1] = "'" + dataGridBook.Rows[j].Cells[k].Value;
                    }
                }
                process.Close();
                MessageBox.Show("导出成功！ ", "系统提示", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
                application.Visible = true;
            }
            catch (Exception ex)
            {
                if (MessageBox.Show(ex.Message + "\n确认使用OPENXMl继续导出吗？", "提示", MessageBoxButtons.OKCancel, MessageBoxIcon.Question, MessageBoxDefaultButton.Button2) == DialogResult.OK)
                {
                    string fpath = ShowSaveFileDialog("导出EXCEL");
                    if (!string.IsNullOrEmpty(fpath))
                    {
                        System.Data.DataTable dr = OpenXMLHelper.GetDgvToTable(dataGridBook);
                        OpenXMLHelper.ExportExcelFile(fpath, dr);
                        MessageBox.Show("导出成功！ ", "系统提示", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
                    }
                }
            }
        }
        public static DataTable OfficeExcelListBox(string excelpath, out string error)
        {
            string sExt = System.IO.Path.GetExtension(excelpath);

            DataTable table = new DataTable();
            string sConn = null;
            if (sExt == ".xlsx" || sExt == ".xls")
            {
                sConn = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + excelpath + ";" + "Extended Properties='Excel 12.0;Persist Security Info=False;HDR=YES'";
            }
            else
            {
                error = "文件格式有误";
                return table;
            }
            try
            {
                OleDbConnection oledbConn = new OleDbConnection(sConn);
                oledbConn.Open();
                table = oledbConn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
                error = "";
                return table;
            }
            catch (Exception ex)
            {
                error = ex.Message;
                return table;
            }
        }
        public static DataTable ReadOfficeExcel(string excelpath, string listBox, out string Error)
        {
            string sExt = System.IO.Path.GetExtension(excelpath);
            string sConn = null;
            if (sExt == ".xlsx" || sExt == ".xls")
            {
                sConn = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + excelpath + ";" + "Extended Properties='Excel 12.0;HDR=YES'";
            }
            else
            {
                Error = "文件格式有误";
                return null;
            }
            try
            {
                OleDbConnection oledbConn = new OleDbConnection(sConn);
                oledbConn.Open();
                OleDbDataAdapter command = new OleDbDataAdapter("SELECT * FROM [" + listBox + "]", oledbConn);
                DataSet ds = new DataSet();
                command.Fill(ds);
                oledbConn.Close();
                Error = "";
                return ds.Tables[0];
            }
            catch (Exception ex)
            {
                Error = ex.Message;
                return null;
            }
        }
    }
}
